# Задачи хакатона
> ### Создание обработчика писем электронной почты
<details>
Цель создания такого продукта —преобразование письма (тема + текст письма) в формализованную заявку Service Desk для агента с выделением сущностей заявки. Задача заключается в том, чтобы изучить несколько тысяч реально полученных агентами писем, создать и обучить на этих письмах сервис искусственного интеллекта, который по тексту любого другого похожего письма, сможет определить заявка это или нет, будет извлекать детали запроса (сущности заявки) и будет способен запрашивать уточнения в ответ.

 

В рамках этой задачи необходимо создать обучаемый обработчик почты, позволяющий:

1. Классифицировать электронный письма на заявки/запросы и все остальные письма (не заявки и не запросы). Заявки можно разделять по типам – Запрос вариантов, Бронирование, Оформление, Отмена, Изменение, Консультация. 

2. В случае если письмо является запросом/заявкой необходимо извлекать (при наличии) следующие сущности:

Тип заявки – Запрос вариантов, Бронирование, Оформление, Отмена, Изменение, Консультация.
Тип поездки (в одну сторону/с возвратом/сложная – с несколькими точками прибытия) – от этого зависит, какой билет должен быть предложен клиенту. Москва-Самара (в одну сторону) или при наличии ключевых слов “обратно” или второй даты будет билет Москва-Самара-Москва (в две стороны). Может быть маршрут, который проходит через несколько городов (сложная поездка)
Дата, время начала поездки или командировки – формат дата, время или временной диапазон (например, 2023-01-21 00:00:00.000)
Дата, время обратной поездки или завершение командировки – формат дата, время или временной диапазон (например, 2023-01-25 00:00:00.000)
Город отправления – ID города по справочнику Аэроклуб или IATA код по справочнику
Город назначения – ID города по справочнику Аэроклуб или IATA код по справочнику
Тип услуги - Авиа, ЖД, Отель, Другое, Смешанная
Уточнения по Авиа (необязательный параметр) – Аэропорт (код IATA – SVO Шереметьево); Рейс (AB1234- обычно две буквы и от 1 до 4 цифр); Класс перелета (эконом, комфорт, бизнес, первый); Авиакомпания (в виде кода по справочнику: Аэрофлот -SU); Наличие багажа.
Уточнения по ЖД поездке (необязательный параметр) – Вокзал(id аэроклуб); Название поезда (Сапсан, Красная стрела..); Тип вагона (СВ, Люкс, Купе, Плацкарт, Сидячее)
Уточнения по Отелю – Название отеля; Звездность (1,2,3,4,5); Тип номера; Район или адрес
Прочие пожелания (необязательный параметр)
Номер бронирования (необязательный параметр) – последовательность из 6 букв и цифр.
 
Примечания: 

В случае, если письмо является заявкой, где тип услуги “смешанный”, или же есть запрос в одном письме на несколько разных билетов, необходимо из одного письма сформировать несколько заявок, релевантных для информации внутри письма. 
При отсутствии параметра (например, город отправления в случае типа услуги “Отель”) корректно оставлять поле пустым или заполнять неопределенным идентификатором (Nan). 
Ограничения и дополнительные функции системы: 

Распознавание именованных сущностей: возможно при относительно точном использовании названий именованных сущностей из Базы данных компании, при незначительных опечатках или при общеизвестных модификациях. Невозможно при значимых опечатках.
Распознавание дат поездки возможно из разных форматов и словесном описании требований
Если при распознавании сущностей заявки недостаточно данных для обработки заявки, предполагается, что система может сделать не более одного текстового уточнения параметров у пользователя в ответном запросе. Ответ пользователя на уточняющий запрос может быть использован для дополнения сущностей первичной заявки.
Пример заявки: https://drive.google.com/file/d/1yunEyAmM-rOZR6aDDjNSXRM408l-qTjF/view?usp=share_link 

Данные: https://drive.google.com/drive/folders/1iW8KrkXZ7CETPCN0A50SMjtQ4LqHVMlq?usp=share_link 
</details> 

> ### Создание сервиса ранжирования предложений Auto Avia
<details>
Цель создания такого продукта — это автоматизация процесса подбора и отправки вариантов перелета  в ответ на соответствующую заявку. Ожидается, что автоматизация процесса формирования оффера дополнительно разгрузит агентов.

Те. нужно создать сервис ранжирования предложений. Сервис, принимая в себя данные о компании, сотруднике + набор вариантов (200-300 наборов рейсов с ценами) должен уметь ранжировать варианты от наиболее релевантного выбора к наименьшему. Участники конкурса должны проанализировать миллионы вариантов перелетов из истории поисков и заказов билетов в Time, историю поисков и предложений, чтобы создать модель сортировки вариантов по вероятности выбора клиентом.

Необходимо реализовать сервис ранжирования, основанный на правилах ранжирования, например: смесь правил (предложить самый недорогой, в СПБ только прямые и т.п.) + обучаемая на основе базы данных запросов (BIG DATA Аэроклуб) и предложений, сформированных вручную.

Необходимо заложить возможность развития сервиса вне рамок текущего Технического задания, а именно оставить возможность будущей разработки: 

Учет имеющихся и новых данных, полезных для увеличения качества работы системы. 
Учет действий агентов по ранжированию таких вариантов в системах бронирования. 
Возможность дообучения модели ранжирования разметчиком.  
 

Данные: https://drive.google.com/drive/folders/1NGvPyOOjuD0va9vvEVg1xAgVuOZdYjGI?usp=share_link 
</details> 

> ### Мои задачи
<details>
   - Найти БД городов(см. in_dataset)
   - Обработать БД с учетом следующего.

------------**************------------

Я плохо поставил задачу: берем таблицу с городами и лефт джойн таблицу со странами, оставив такие поля в итоговой таблице

Используемые коды вместо XX
ru - Русский
en - Английский

Таблица _cities:
city_id - Id города
region_id - Id региона   МОЖЕТ БЫТЬ NULL. ПРИСУТСТВУЮТ ГОРОДА БЕЗ РЕГИОНА
country_id - Id страны
important - для крупных населенных пунктов будет TRUE.
title_XX - название населенного пункта, где XX = код страны.
region_XX - название региона, где XX = код страны, переведено практически везде. Может быть NULL.
area_XX - поле названия района, где XX = код страны, переведено практически везде. Может быть NULL.

Таблица _countries:
country_id - Id страны, PK
title_XX - название страны, где XX = код страны.

------------**************------------

Ааа, т.е. выбираем мы города из _cities которая должна быть главной т.к. лефт джоин с _country по полям  country_id по которым организована связь 1:М. 🤪Понил чего тут не понятного.
</details>

> ### Описание решения
- Заполнение стран и регионов осуществляется на основе исходных дампов `_countries.sql` и `_regions.sql`.

- Код Python `Insert_cities.py` итерирует строки самого объемного `_cities.csv` и загружает данные в развернутую по схеме `tables_mod.sql` (я немного исправил исходник для установления связей 1:M, его я тоже скинул) базу данных (см. ER-диаграмму рис. Cities-public). 

- Далее код Python осуществляет лефт джоин `_cities` и `_countries` и выгружает итоговый результат в файл `out_cities.csv`.



